package com.enhinck.dbutilweb.config;

import com.enhinck.dbutilweb.controller.FileEntity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.IOUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.*;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
import java.util.jar.JarEntry;
import java.util.jar.JarFile;

/**
 * <p>sql脚本执行工具
 *
 * @author huenbin
 * @since 2022-03-14 09:52
 */
@Slf4j
public class SqlExecUtil {
    /**
     * 默认使用的类加载器
     */
    private static ClassLoader classLoader = SqlExecUtil.class.getClassLoader();
    public static final String JLIB_RESOURCES = "file:/app/resources/";
    public static final String JLIB_CLASSES = "file:/app/classes/";


    public static final String FILE = "file";
    public static final String JAR = "jar";
    private static Boolean isJlib = false;

    /**
     * 读取 SQL 文件，获取 SQL 语句
     *
     * @param sqlFile SQL 脚本文件
     * @return List<sql> 返回所有 SQL 语句的 List
     * @throws Exception
     */
    private static List<String> loadSql(File sqlFile, Integer startLine) throws Exception {
        List<String> sqlList = new ArrayList<>();
        try {
            InputStream sqlFileIn = new FileInputStream(sqlFile);
            StringBuffer sqlSb = new StringBuffer();
            byte[] buff = new byte[1024];
            int byteRead = 0;
            while ((byteRead = sqlFileIn.read(buff)) != -1) {
                sqlSb.append(new String(buff, 0, byteRead));
            }

            // Windows 下换行是 /r/n, Linux 下是 /n
            String[] sqlArr = sqlSb.toString().split("(;//s*//r//n)|(;//s*//n)");
            for (int i = 0; i < sqlArr.length; i++) {
                String sql = sqlArr[i].replaceAll("--.*", "").trim();
                if (!sql.equals("")) {
                    sqlList.add(sql);
                }
            }

            List<String> result = new ArrayList<>();
            if (sqlList.size() > startLine) {
                for (int i = startLine; i < sqlList.size(); i++) {
                    result.add(sqlList.get(i));
                }
            }
            return result;
        } catch (Exception ex) {
            throw new Exception(ex.getMessage());
        }
    }


    private static List<String> loadSql(String ddl) throws Exception {
        List<String> sqlList = new ArrayList<>();
        try {
            StringBuffer sqlSb = new StringBuffer(ddl);
            // Windows 下换行是 /r/n, Linux 下是 /n
            String[] sqlArr = sqlSb.toString().split("(;//s*//r//n)|(;//s*//n)");
            for (int i = 0; i < sqlArr.length; i++) {
                String sql = sqlArr[i].replaceAll("--.*", "").trim();
                if (!sql.equals("")) {
                    sqlList.add(sql);
                }
            }
            return sqlList;
        } catch (Exception ex) {
            throw new Exception(ex.getMessage());
        }
    }




    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public static class Result {
        private Integer count;
        private String msg;
        private Boolean success;
    }

    /**
     * 传入连接来执行 SQL 脚本文件，这样可与其外的数据库操作同处一个事物中
     *
     * @param conn    传入数据库连接
     * @param sqlFile SQL 脚本文件
     * @throws Exception
     */
    public static Result execute(Connection conn, File sqlFile, Integer startLine) {
        Statement stmt = null;
        try {
            conn.setAutoCommit(false);
            List<String> sqlList = loadSql(sqlFile, startLine);
            if (sqlList.size() == 0) {
                log.info("脚本未更新：{}", sqlFile.getName());
                return new Result(0, "脚本未更新", false);
            }
            stmt = conn.createStatement();
            for (String sql : sqlList) {
                stmt.addBatch(sql);
            }
            int[] rows = stmt.executeBatch();
            conn.commit();
            int executeCount = sqlList.size();
            log.info("执行脚本完成{},起始行数{},执行行数：{} Row count:{}", sqlFile, startLine, executeCount, Arrays.toString(rows));
            return new Result(executeCount, "执行脚本完成", true);
        } catch (Exception e) {
            log.error("{}", e);
            try {
                conn.rollback();
            } catch (SQLException ex) {
                log.error(" conn.rollback(){}", e);
            }
            return new Result(0, "脚本执行错误", false);
        } finally {
            try {
                conn.setAutoCommit(true);
            } catch (SQLException e) {
                log.info("conn.setAutoCommit(true) error:{}", e);
            }
            releaseConnection(null, stmt, null);
        }
    }


    public static void releaseConnection(Connection con,
                                         Statement pstmt, ResultSet rs) {
        try {
            if (con != null) {
                con.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    /**
     * 获取包下所有实现了superStrategy的类并加入list
     */
    public static List<File> scanSql(String filePath) {
        URL url = classLoader.getResource(filePath);
        String protocol = url.getProtocol();
        List<File> files = null;
        if (isJlib) {
            files = findJlibLocal(filePath,".sql");
            log.info("findJlibSqlLocal");
        } else if (FILE.equals(protocol)) {
            // 本地自己可见的代码
            files = findLocal(filePath,".sql");
            log.info("findSqlLocal");
        } else if (JAR.equals(protocol)) {
            // 引用jar包的代码
            files = findJar(filePath,".sql");
            log.info("findSqlJar");
        }

        Collections.sort(files, Comparator.comparing(File::getName));

        return files;
    }

    public static List<FileEntity> scanTemplate(String filePath) {
        URL url = classLoader.getResource(filePath);
        String protocol = url.getProtocol();
        List<FileEntity> files = null;
        if (isJlib) {
            files = findJlibLocalFileEntity(filePath,".ftl");
            log.info("scanTemplateJlibLocal");
        } else if (FILE.equals(protocol)) {
            // 本地自己可见的代码
            files = findLocalFileEntity(filePath,".ftl");
            log.info("scanTemplateLocal");
        } else if (JAR.equals(protocol)) {
            // 引用jar包的代码
            files = findJarFileEntity(filePath,".ftl");
            log.info("scanTemplateJar");
        }

        Collections.sort(files, Comparator.comparing(FileEntity::getFileName));

        return files;
    }

    /**
     * 本地JLIB打包方式查找
     *
     * @param path
     */
    private static List<File> findJlibLocal(final String path,final String ext) {
        URI url = null;
        try {
            url = new URL(JLIB_RESOURCES + path).toURI();
            log.info("{}", url);
        } catch (MalformedURLException | URISyntaxException e1) {
            throw new RuntimeException("未找到策略资源");
        }
        List<File> files = new ArrayList<>();
        File file = new File(url);
        for (File chiFile : file.listFiles()) {
            if (chiFile.getName().endsWith(ext)) {
                files.add(chiFile);
            }
        }

        return files;
    }

    private static List<FileEntity> findJlibLocalFileEntity(final String path,final String ext) {
        URI url = null;
        try {
            url = new URL(JLIB_RESOURCES + path).toURI();
            log.info("{}", url);
        } catch (MalformedURLException | URISyntaxException e1) {
            throw new RuntimeException("未找到策略资源");
        }
        List<FileEntity> files = new ArrayList<>();
        File file = new File(url);
        for (File chiFile : file.listFiles()) {
            if (chiFile.getName().endsWith(ext)) {
                try {
                    FileEntity fileEntity = new FileEntity();
                    String fileContent  = FileUtils.readFileToString(chiFile,"UTF-8");
                    fileEntity.setFileName(chiFile.getName());
                    fileEntity.setFileContent(fileContent);
                    files.add(fileEntity);
                } catch (IOException e) {
                    log.error("",e);
                }
            }
        }

        return files;
    }


    /**
     * 本地查找
     *
     * @param path
     */
    private static List<FileEntity> findLocalFileEntity(final String path,String ext) {
        URI url = null;
        try {
            url = classLoader.getResource(path).toURI();
        } catch (URISyntaxException e1) {
            throw new RuntimeException("未找到策略资源");
        }
        List<FileEntity> files = new ArrayList<>();
        File file = new File(url);
        for (File chiFile : file.listFiles()) {
            if (chiFile.getName().endsWith(ext)) {
                try {
                    FileEntity fileEntity = new FileEntity();
                    String fileContent  = FileUtils.readFileToString(chiFile,"UTF-8");
                    fileEntity.setFileName(chiFile.getName());
                    fileEntity.setFileContent(fileContent);
                    files.add(fileEntity);
                } catch (IOException e) {
                    log.error("",e);
                }
            }
        }

        return files;
    }

    private static List<File> findLocal(final String path,String ext) {
        URI url = null;
        try {
            url = classLoader.getResource(path).toURI();
        } catch (URISyntaxException e1) {
            throw new RuntimeException("未找到策略资源");
        }

        List<File> files = new ArrayList<>();
        File file = new File(url);
        for (File chiFile : file.listFiles()) {
            if (chiFile.getName().endsWith(ext)) {
                files.add(chiFile);
            }
        }

        return files;
    }

    /**
     * jar包查找
     *
     * @param path
     */
    private static List<File> findJar(final String path, String ext) {
        JarFile jarFile = null;
        JarURLConnection jarURLConnection = null;
        String parentPath;
        try {
            URL url = classLoader.getResource(path);
            jarURLConnection = (JarURLConnection) url.openConnection();
            jarFile = jarURLConnection.getJarFile();
            parentPath = url.toString();
        } catch (IOException e) {
            throw new RuntimeException("未找到策略资源");
        }
        List<File> files = new ArrayList<>();
        Enumeration<JarEntry> jarEntries = jarFile.entries();
        while (jarEntries.hasMoreElements()) {
            JarEntry jarEntry = jarEntries.nextElement();
            String jarEntryName = jarEntry.getName();
            if (jarEntryName.contains(path)) {
                //递归遍历子目录
                if (!jarEntry.isDirectory()) {
                    if (jarEntry.getName().endsWith(ext)) {
                        File file = new File(jarEntry.getName());
                        files.add(file);
                    }
                }
            }
        }
        return files;
    }


    private static List<FileEntity> findJarFileEntity(final String path, String ext) {
        JarFile jarFile = null;
        JarURLConnection jarURLConnection = null;
        String parentPath;
        try {
            URL url = classLoader.getResource(path);
            jarURLConnection = (JarURLConnection) url.openConnection();
            jarFile = jarURLConnection.getJarFile();
            parentPath = url.toString();
        } catch (IOException e) {
            throw new RuntimeException("未找到策略资源");
        }
        List<FileEntity> files = new ArrayList<>();
        Enumeration<JarEntry> jarEntries = jarFile.entries();
        while (jarEntries.hasMoreElements()) {
            JarEntry jarEntry = jarEntries.nextElement();
            String jarEntryName = jarEntry.getName();
            if (jarEntryName.contains(path)) {
                //递归遍历子目录
                if (!jarEntry.isDirectory()) {
                    if (jarEntry.getName().endsWith(ext)) {
                            try {
                                FileEntity fileEntity = new FileEntity();
                                InputStream sqlFileIn = jarFile.getInputStream(jarEntry);
                                String fileContent  = IOUtils.toString(sqlFileIn,"UTF-8");
                                fileEntity.setFileName(jarEntry.getName().split("/")[1]);
                                fileEntity.setFileContent(fileContent);
                                files.add(fileEntity);
                            } catch (IOException e) {
                                log.error("",e);
                            }
                      //  log.info(jarEntry.getName());
                        //File file = new File(jarEntry.getName());
                       // files.add(file);
                    }
                }
            }
        }
        return files;
    }


    public static void main(String[] args) throws Exception {

        List<File> files = SqlExecUtil.scanSql("initSql");

        files.forEach(file -> log.info("{}", file.getName()));
//        File file = new File("/Users/xiaomi/work/20210926/angel/angel-starter/src/main/resources/sql/test.sql");
//        List<String> sqlList = new SqlExecUtil().loadSql(file, 0);
//        System.out.println("size:" + sqlList.size());
//        for (String sql : sqlList) {
//            System.out.println(sql);
//        }
    }

    public static List<File> getDataSql() {
        return scanSql("dataSql");
    }

    public static List<File> getInitSql() {
        return scanSql("initSql");
    }



    public static Result execute(Connection conn, String ddl) {
        Statement stmt = null;
        try {
            conn.setAutoCommit(false);
            List<String> sqlList = loadSql(ddl);
            if (sqlList.size() == 0) {
                log.info("脚本未更新：{}", ddl);
                return new Result(0, "脚本未更新", false);
            }
            stmt = conn.createStatement();
            for (String sql : sqlList) {
                stmt.addBatch(sql);
            }
            int[] rows = stmt.executeBatch();
            conn.commit();
            int executeCount = sqlList.size();
            log.info("执行脚本完成{},起始行数{},执行行数：{} Row count:{}", ddl, executeCount, Arrays.toString(rows));
            return new Result(executeCount, "执行脚本完成", true);
        } catch (Exception e) {
            log.error("{}", e);
            try {
                conn.rollback();
            } catch (SQLException ex) {
                log.error(" conn.rollback(){}", e);
            }
            return new Result(0, "脚本执行错误", false);
        } finally {
            try {
                conn.setAutoCommit(true);
            } catch (SQLException e) {
                log.info("conn.setAutoCommit(true) error:{}", e);
            }
            releaseConnection(null, stmt, null);
        }
    }

}
